package com.example.financeapi.config.db;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;

import javax.sql.DataSource;
import java.sql.*;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class JdbcDruidUtils {

    public static Map<String,Object> updateByPreparedStatement(DataSource dataSource, String sql, List<Object> params) throws SQLException {
        long start =System.currentTimeMillis();
        Map<String,Object> map = new HashMap<>();
        Connection connection =  dataSource.getConnection();
        int result = -1;
        PreparedStatement pstmt = null;
        try {
            pstmt = connection.prepareStatement(sql);
            int index = 1;
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            result = pstmt.executeUpdate();
            map.put("data",result);
            map.put("times",System.currentTimeMillis()-start);
        } catch (SQLException e) {
            log.error("updateByPreparedStatement 执行异常"+e.getMessage(),e);
            map.put("data","执行失败,无返回结果."+e.getMessage());
        } finally {
            releaseConn(null, pstmt, connection);
        }
        return map;

    }

    public static Map<String,Object> findMoreResult(DataSource dataSource, String sqlTxt, List<Object> params) throws SQLException {
        long start =System.currentTimeMillis();
        Connection connection = dataSource.getConnection();
        Map<String,Object> map = new HashMap<>();
        JSONArray list = new JSONArray();
        int index = 1;
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try{
            //sqlTxt ="SELECT * FROM ("+sqlTxt+") WHERE ROWNUM<10000";
            pstmt = connection.prepareStatement(sqlTxt);
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int cols_len = metaData.getColumnCount();
            String cols_name;
            Object cols_value;
            while (resultSet.next()) {
                JSONObject json = new JSONObject(new LinkedHashMap());
                for (int i = 0; i < cols_len; i++) {
                    cols_name = metaData.getColumnLabel(i + 1);
                    cols_value = resultSet.getObject(cols_name);
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    json.put(cols_name, cols_value);
                }
                list.add(json);
            }
            map.put("data",list);
            map.put("times",System.currentTimeMillis()-start);
        }catch (Exception e){
            log.error("findMoreResult 执行异常 msg={},e={}",e.getMessage(),e);
            map.put("data","执行失败,无返回结果."+e.getMessage());
        }finally {
            releaseConn(resultSet, pstmt, connection);
        }
        return map;
    }

    /**
    * @param sql 执行sql
    * @param params 参数
    * @return
    */
    public static Map<String,Object> updateTimers(DataSource dataSource, String sql, List<Object> params,StringBuffer field) throws SQLException {
        Connection connection =dataSource.getConnection();
        Map<String,Object> map = new HashMap<>();
        map.put("code","1");
        map.put("msg","执行成功");
        PreparedStatement pstmt = null;
        try{
            pstmt = connection.prepareStatement(sql);
            String[] items = field.toString().split(",");
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    int index = 1;
                    JSONObject item1 = (JSONObject) params.get(i);
                    for (int j = 0; j <items.length ; j++) {
                        Object itemObj = item1.get(items[j]);
                        pstmt.setObject(index++, itemObj);
                    }
                    pstmt.addBatch();//添加到批次
                }
                pstmt.executeBatch();
            }else{
                throw new NullPointerException("属性数据为空，无效插入.");
            }
        }catch (Exception e){
            log.error("updateTimers 执行异常"+e.getMessage(),e);
            map.put("code","2");
            map.put("msg",e.getMessage());
        }finally {
            releaseConn(null, pstmt, connection);
        }
        return map;

    }

    /**
     * 未使用。 通过count(1)获取记录总数
     * @param sql
     * @param params
     * @return
     */
    public static long getCount(DataSource dataSource, String sql, List<Object> params) throws SQLException {
        Connection connection =  dataSource.getConnection();
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        long count = 0L;
        try {
            pstmt = connection.prepareStatement(sql);
            int index = 1;
            if (params != null && !params.isEmpty()) {
                for (int i = 0; i < params.size(); i++) {
                    pstmt.setObject(index++, params.get(i));
                }
            }
            resultSet = pstmt.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getLong(1);
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
        } finally {
            releaseConn(resultSet, pstmt, connection);
        }
        return count;

    }

    /**
     * 关闭连接
     * @param resultSet
     * @param pstmt
     * @param connection
     */
    public static void releaseConn(ResultSet resultSet, PreparedStatement pstmt, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                log.error(e.getMessage());
            }
        }
    }


    /**
     *  获取数据库连接
     * @param dataSource
     * @return
     * @throws SQLException
     */
    public static Connection getConnection(DruidDataSource dataSource) throws SQLException {
        return dataSource.getConnection();
    }


}
